﻿using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace EarringsRecommendationWeb
{
    public partial class index3 : CheckUserSession
    {
        #region Private Variable
        private MemberEarrings _memEarrings = null;
        private string _personalid = string.Empty;
        private string _faceShape = string.Empty;        
        private string _personalimgpath = string.Empty;
        private string _rearlobeposition = string.Empty;
        private string _learlobeposition = string.Empty;
        private string _earlobe = string.Empty;
        private string _earshape = string.Empty;
        private string _hairstyle = string.Empty;
        private string _skincolor = string.Empty;
        private string _neck = string.Empty;
        private string _cdWidth = string.Empty;
        private string _pointjson = string.Empty;

        private string _sqlFaceShape = string.Empty;
        private string _sqlEarringsShape = string.Empty;
        private string _sqlNeck = string.Empty;
        private string _sqlEarlobe = string.Empty;
        private string _sqlEarShape = string.Empty;
        private string _sqlHairStyle = string.Empty;
        private string _sqlSkinColor = string.Empty;
        

        #endregion


        protected void Page_Load(object sender, EventArgs e)
        {
            base.Page_Load(sender, e);
            //Page.GetPostBackEventReference(btn1);
            Member mem = Session["member"] as Member;

            if (mem != null)
            {
                Response.Write("<input id='hidden-email' type='hidden' value='" + mem.Email + "'>");
                Response.Write("<input id='hidden-name' type='hidden' value='" + mem.Name + "'>");
            }

            if (!IsPostBack)
            {
                _personalid = Session["personalId"].ToString();

                if (!string.IsNullOrEmpty(_personalid))
                {
                    _memEarrings = GlobalFunction.GetMemberEarrings(_personalid);

                    _faceShape = _memEarrings.FaceShape; //Request["faceshape"];
                    _personalimgpath = "Upload/" + _memEarrings.PersonalId + ".jpg";
                    _rearlobeposition = _memEarrings.REarlobePosition; //Request["rightearlobeposition"];
                    _learlobeposition = _memEarrings.LEarlobePosition; //Request["leftearlobeposition"];
                    _earlobe = _memEarrings.Earlobe; //Request["earlobe"];
                    _earshape = _memEarrings.EarShape; //Request["earshape"];
                    _hairstyle = _memEarrings.HairStyle; //Request["hairstyle"];
                    _skincolor = _memEarrings.SkinColor; //Request["skincolor"];
                    _neck = _memEarrings.Neck; //Request["neck"];
                    _cdWidth = _memEarrings.CDWidth;

                    //Session["personalId"] = null;
                    //Session["faceshape"] = null;
                    //Session["neck"] = null;               
                    //Session["rightearlobeposition"] = null;
                    //Session["leftearlobeposition"] = null;
                    //Session["PointJSON"] = null;

                    string faceShapeTH = this.GetFaceShapeTH();
                    ClientScript.RegisterArrayDeclaration("Faceshape", JsonConvert.SerializeObject(faceShapeTH));

                    ClientScript.RegisterArrayDeclaration("CDWidth", JsonConvert.SerializeObject(_cdWidth));

                    //Get Earrings
                    this.GetSQLStringCondition();
                    DataTable[] dtResultArray = new DataTable[4];
                    dtResultArray = this.GetEarringsSuitability();

                    if (dtResultArray.Length > 0)
                    {
                        //Earrings
                        DataTable dtEarringsSuggest = dtResultArray[0];
                        ClientScript.RegisterArrayDeclaration("EarringsSuggest", JsonConvert.SerializeObject(dtEarringsSuggest));

                        //Earrings Style
                        DataTable dtEarringsStyles = dtResultArray[1];
                        ClientScript.RegisterArrayDeclaration("EarringsStyles", JsonConvert.SerializeObject(dtEarringsStyles));

                        string stylesOk = string.Empty;

                        foreach (DataRow  row in dtEarringsStyles.Rows)
                        {
                            if(string.IsNullOrEmpty(stylesOk))
                            {
                                stylesOk = stylesOk + row.ItemArray[1].ToString();
                            }
                            else
                            {
                                stylesOk = stylesOk + ", " + row.ItemArray[1].ToString();
                            }
                            
                        }

                        //Earrings Color
                        DataTable dtEarringsColor = dtResultArray[2];
                        ClientScript.RegisterArrayDeclaration("EarringsColor", JsonConvert.SerializeObject(dtEarringsColor));

                        string colorOk = string.Empty;

                        foreach (DataRow row in dtEarringsColor.Rows)
                        {
                            if (string.IsNullOrEmpty(stylesOk))
                            {
                                colorOk = colorOk + row.ItemArray[1].ToString();
                            }
                            else
                            {
                                colorOk = colorOk + ", " + row.ItemArray[1].ToString();
                            }

                        }

                        //Earrings Gemstone
                        DataTable dtEarringsGemstone = dtResultArray[3];
                        ClientScript.RegisterArrayDeclaration("EarringsGemstone", JsonConvert.SerializeObject(dtEarringsGemstone));

                        //String Array
                        string[] value = { _personalimgpath, _rearlobeposition, _learlobeposition };
                        ClientScript.RegisterArrayDeclaration("EarPosition", JsonConvert.SerializeObject(value));

                        string message = string.Empty;
                        if(!string.IsNullOrEmpty(stylesOk))
                        {
                            message = message +"รูปแบบต่างหูที่เหมาะกับคุณ คือ " + stylesOk + "<br />";
                        }

                        if (!string.IsNullOrEmpty(colorOk))
                        {
                            message = message + " สีต่างหูที่เหมาะกับคุณ คือ " + colorOk + "<br />"; 
                        }

                        //message = message; // +"ทางร้านขอขอบคุณลูกค้าทุกท่านที่ไว้วางใจเลือกซื้อเครื่องประดับกับเรา ทางเราหวังเป็นอย่างยิ่งว่าท่านจะกลับมาใช้บริการกับทางเราอีกครั้ง";

                        ClientScript.RegisterArrayDeclaration("DescriptionMessage", JsonConvert.SerializeObject(message));
                    }
                }
                else
                {
                    //ยังไม่ได้คำนวณรูปหน้า
                    string message = HttpUtility.UrlEncode("กรุณาคำนวณรูปหน้าก่อน");
                    //ClientScript.RegisterHiddenField("AlertMessage", message);
                    Response.Redirect("index.aspx?msg=" + message);
                }
            }                   
        }

        private DataTable[] GetEarringsSuitability()
        {
            //this.GetFirstFaceShape();

            SqlParameter[] sqlParams = new SqlParameter[] 
                {
                    new SqlParameter("@FASHP", _faceShape),
                    new SqlParameter("@NECKS", _neck),                    
                    new SqlParameter("@EARLB", _earlobe),                
                    new SqlParameter("@EASHP", _earshape),
                    new SqlParameter("@HRSTY", _hairstyle),
                    new SqlParameter("@SKCOR", _skincolor)
                };

            StringBuilder sqlStr = new StringBuilder();

            sqlStr.Append("SELECT * , 'True' AS [Suggest]");
            sqlStr.Append(" ,(SELECT config_name FROM earringsconfig eacon WHERE Er_Style = eacon.config_id) AS [StyleName]");
            sqlStr.Append("  FROM Earrings  WHERE Er_Style IN ( ");
            //---Earrings Styles---
            //FaceShape
            if (_sqlFaceShape.Length > 0)
            {
                sqlStr.Append(_sqlFaceShape);                
            }
            
            //Neck
            if (_sqlNeck.Length > 0 )
            {
                sqlStr.Append(" INTERSECT ");
                sqlStr.Append(_sqlNeck);
            }
            //Earlobe
            if(_sqlEarlobe.Length > 0)
            {
                sqlStr.Append(" INTERSECT ");
                sqlStr.Append(_sqlEarlobe);
            }
            //EarShp
            if(_sqlEarShape.Length > 0 )
            {
                sqlStr.Append(" INTERSECT ");    
                sqlStr.Append(_sqlEarShape);
            }
            //HairStyle
            if(_sqlHairStyle.Length > 0)
            {
                sqlStr.Append(" INTERSECT ");
                sqlStr.Append(_sqlHairStyle);
            }
            sqlStr.Append(" ) ");
            //---Earrings Shape---
            if (_sqlEarringsShape.Length > 0)
            {
                sqlStr.Append(" AND er_shape IN ( ");
                sqlStr.Append(_sqlEarringsShape);
                sqlStr.Append(" )");
            }
            //Earrings Color
            if(_sqlSkinColor.Length > 0)
            {
                sqlStr.Append(" AND er_color IN (");
                sqlStr.Append(_sqlSkinColor);
                sqlStr.Append(" )");
            }

            //Earrings Suggestion
            DataTable dtSuggest = new DataTable();
            dtSuggest  = SqlClientUtility.ExecuteDataTable(CommandType.Text, sqlStr.ToString(), sqlParams);

            DataTable dtNoSuggest = new DataTable();
            dtNoSuggest = this.GetEarringsNoSuggestion(sqlStr.ToString());

            if (dtNoSuggest != null)
            {
                dtSuggest.Merge(dtNoSuggest);
            }

            //------------------

            DataTable[] dtArray = new DataTable[4];
            //Earrings
            dtArray[0] = new DataTable("Earrings");
            dtArray[0] = dtSuggest;

            //Earrings Styles
            dtArray[1] = new DataTable("Style");
            dtArray[1] = this.GetEarringsStyleSuggestion(sqlStr.ToString());

            //Earrings Color
            dtArray[2] = new DataTable("Color");
            dtArray[2] = this.GetEarringsColorSuggestion(sqlStr.ToString());

            //Earrings Styles
            dtArray[3] = new DataTable("Gemstone");
            dtArray[3] = this.GetEarringsGemstoneSuggestion(sqlStr.ToString());

            if (dtArray.Length > 0)
            {
                return dtArray;
            }
            else
            {
                return null;
            }
            
        }


        private void GetSQLStringCondition()
        {
            StringBuilder sqlStr = new StringBuilder();
            
            //FaceShape
            sqlStr.Clear();
            if (!string.IsNullOrEmpty(_faceShape))
            {
                sqlStr.Append(" SELECT easut.config_id1 AS [Earring Style] FROM earringssuitability easut");
                sqlStr.Append(" WHERE easut.short_type1 = 'ERSTY' AND easut.short_type2 = 'FASHP' ");
                sqlStr.Append(" AND easut.config_id2 = (SELECT config_id FROM earringsconfig WHERE short_type ='FASHP' AND config_name = @FASHP)");
                sqlStr.Append(" AND rating <> 0");
                _sqlFaceShape = sqlStr.ToString();
            }

            //Earring Shape
            sqlStr.Clear();
            if (!string.IsNullOrEmpty(_earshape))
            {
                sqlStr.Append(" SELECT easut.config_id1 as [Earring Shape] FROM earringssuitability easut ");
                sqlStr.Append(" WHERE easut.short_type1 = 'ERSHP' AND easut.short_type2 = 'FASHP' ");
                sqlStr.Append(" AND easut.config_id2 = (SELECT config_id FROM earringsconfig WHERE short_type ='FASHP' AND config_name = @FASHP) ");
                sqlStr.Append(" AND rating <> 0");
                _sqlEarringsShape = sqlStr.ToString();
            }

            //Neck
            sqlStr.Clear();
            if (!string.IsNullOrEmpty(_neck))
            {
                sqlStr.Append(" SELECT easut.config_id1 AS [Earring Style] FROM earringssuitability easut");
                sqlStr.Append(" WHERE easut.short_type1 = 'ERSTY' AND easut.short_type2 = 'NECKS' ");
                sqlStr.Append(" AND easut.config_id2 = (SELECT config_id FROM earringsconfig WHERE short_type ='NECKS' AND config_name = @NECKS)");
                sqlStr.Append(" AND rating <> 0");
                _sqlNeck = sqlStr.ToString();
            }

            //Earlobe
            sqlStr.Clear();
            if(!string.IsNullOrEmpty(_earlobe))
            {
                sqlStr.Append(" SELECT easut.config_id1 AS [Earring Style] FROM earringssuitability easut ");
                sqlStr.Append(" WHERE easut.short_type1 = 'ERSTY' AND easut.short_type2 = 'EARLB' ");
                sqlStr.Append(" AND easut.config_id2 = (SELECT config_id FROM earringsconfig WHERE short_type ='EARLB' AND config_name = @EARLB)");
                sqlStr.Append(" AND rating <> 0");
                _sqlEarlobe = sqlStr.ToString();
            }

            //EarShape
            sqlStr.Clear();
            if(!string.IsNullOrEmpty(_earshape))
            {
                sqlStr.Append(" SELECT easut.config_id1 AS [Earring Style] FROM earringssuitability easut ");
                sqlStr.Append(" WHERE easut.short_type1 = 'ERSTY' AND easut.short_type2 = 'EASHP' ");
                sqlStr.Append(" AND easut.config_id2 = (SELECT config_id FROM earringsconfig WHERE short_type ='EASHP' AND config_name = @EASHP)");
                sqlStr.Append(" AND rating <> 0");
                _sqlEarShape = sqlStr.ToString();
            }

            //HairStyle
            sqlStr.Clear();
            if(!string.IsNullOrEmpty(_hairstyle))
            {
                sqlStr.Append(" SELECT easut.config_id1 AS [Earring Style] FROM earringssuitability easut");
                sqlStr.Append(" WHERE easut.short_type1 = 'ERSTY' AND easut.short_type2 = 'HRSTY' ");
                sqlStr.Append(" AND easut.config_id2 = (SELECT config_id FROM earringsconfig WHERE short_type ='HRSTY' AND config_name = @HRSTY) ");
                sqlStr.Append(" AND rating <> 0");
                _sqlHairStyle = sqlStr.ToString();
            }

            //Skin Color
            sqlStr.Clear();
            if(!string.IsNullOrEmpty(_skincolor))
            {
                sqlStr.Append(" SELECT easut.config_id1 AS [Earring Style] FROM earringssuitability easut");
                sqlStr.Append(" WHERE easut.short_type1 = 'JWCOR' AND easut.short_type2 = 'SKCOR' ");
                sqlStr.Append(" AND easut.config_id2 = (SELECT config_id FROM earringsconfig WHERE short_type ='SKCOR' AND config_name = @SKCOR) ");
                sqlStr.Append(" AND rating <> 0");
                _sqlSkinColor = sqlStr.ToString();
            }
        }

        private DataTable GetEarringsNoSuggestion (string sqlStr)
        {
            SqlParameter[] sqlParams = new SqlParameter[] 
                {
                    new SqlParameter("@FASHP", _faceShape),
                    new SqlParameter("@NECKS", _neck),                    
                    new SqlParameter("@EARLB", _earlobe),                
                    new SqlParameter("@EASHP", _earshape),
                    new SqlParameter("@HRSTY", _hairstyle),
                    new SqlParameter("@SKCOR", _skincolor)
                };

            StringBuilder sqlStrN = new StringBuilder();
            sqlStrN.Append(" SELECT * ,'False' AS [Suggest]");
            sqlStrN.Append(" ,(SELECT config_name FROM earringsconfig eacon WHERE er.Er_Style = eacon.config_id) AS [StyleName]");
            sqlStrN.Append(" FROM  Earrings er WHERE ER_Code NOT IN");
            sqlStrN.Append(" ( SELECT DISTINCT Er_Code FROM ( ");
            sqlStrN.Append(sqlStr);
            sqlStrN.Append(" )tb)");

            DataTable dtResult = new DataTable();
            dtResult = SqlClientUtility.ExecuteDataTable(CommandType.Text, sqlStrN.ToString(), sqlParams);

            if (dtResult.Rows.Count > 0)
            {
                return dtResult;
            }
            else
            {
                return null;
            }
        }

        private DataTable GetEarringsStyleSuggestion(string sqlStr)
        {
            SqlParameter[] sqlParams = new SqlParameter[] 
                {
                    new SqlParameter("@FASHP", _faceShape),
                    new SqlParameter("@NECKS", _neck),                    
                    new SqlParameter("@EARLB", _earlobe),                
                    new SqlParameter("@EASHP", _earshape),
                    new SqlParameter("@HRSTY", _hairstyle),
                    new SqlParameter("@SKCOR", _skincolor)
                };                     

            StringBuilder sqlStrN = new StringBuilder();
            sqlStrN.Append(" SELECT DISTINCT Er_Style AS [StyleId],eacon.config_name AS [StyleName] FROM ( ");
            sqlStrN.Append(sqlStr);
            sqlStrN.Append( " ) tb");
            sqlStrN.Append(" LEFT JOIN earringsconfig eacon ON tb.Er_Style = eacon.config_id");

            DataTable dtResult = new DataTable();
            dtResult = SqlClientUtility.ExecuteDataTable(CommandType.Text, sqlStrN.ToString(),sqlParams);

            if (dtResult.Rows.Count > 0)
            {
                return dtResult;
            }
            else
            {
                return null;
            }
        }

        private DataTable GetEarringsColorSuggestion(string sqlStr)
        {
            SqlParameter[] sqlParams = new SqlParameter[] 
                {
                    new SqlParameter("@FASHP", _faceShape),
                    new SqlParameter("@NECKS", _neck),                    
                    new SqlParameter("@EARLB", _earlobe),                
                    new SqlParameter("@EASHP", _earshape),
                    new SqlParameter("@HRSTY", _hairstyle),
                    new SqlParameter("@SKCOR", _skincolor)
                };

            StringBuilder sqlStrN = new StringBuilder();
            sqlStrN.Append(" SELECT DISTINCT Er_Color AS [ColorId],eacon.config_nameTh AS [ColorName],eacon.Config_Image AS [ColorImgName] FROM ( ");
            sqlStrN.Append(sqlStr);
            sqlStrN.Append(" ) tb");
            sqlStrN.Append(" LEFT JOIN earringsconfig eacon ON tb.Er_Color = eacon.config_id");

            DataTable dtResult = new DataTable();
            dtResult = SqlClientUtility.ExecuteDataTable(CommandType.Text, sqlStrN.ToString(), sqlParams);

            if (dtResult.Rows.Count > 0)
            {
                foreach (DataRow rowItem in dtResult.Rows)
                {
                    string imgPath = "Data/Color/" + Convert.ToString(rowItem["ColorImgName"]);
                    rowItem["ColorImgName"] = imgPath;
                }
                
                return dtResult;
            }
            else
            {
                return null;
            }
        }

        private DataTable GetEarringsGemstoneSuggestion(string sqlStr)
        {            
            SqlParameter[] sqlParams = new SqlParameter[] 
                {
                    new SqlParameter("@FASHP", _faceShape),
                    new SqlParameter("@NECKS", _neck),                    
                    new SqlParameter("@EARLB", _earlobe),                
                    new SqlParameter("@EASHP", _earshape),
                    new SqlParameter("@HRSTY", _hairstyle),
                    new SqlParameter("@SKCOR", _skincolor)
                };

            StringBuilder sqlStrN = new StringBuilder();
            sqlStrN.Append(" SELECT DISTINCT Er_Gemstone AS [Gemstone] FROM ( ");
            sqlStrN.Append(sqlStr);
            sqlStrN.Append(" ) tb");
            sqlStrN.Append(" WHERE tb.Er_Gemstone is not null");

            DataTable dtResult = new DataTable();
            dtResult = SqlClientUtility.ExecuteDataTable(CommandType.Text, sqlStrN.ToString(), sqlParams);

            if (dtResult.Rows.Count > 0)
            {
                return dtResult;
            }
            else
            {
                return null;
            }
        }

        private string GetFaceShapeTH()
        {
            SqlParameter[] sqlParams = new SqlParameter[] 
                {
                    new SqlParameter("@FASHP", _faceShape)
                };
            string sql = "SELECT Config_NameTh FROM EarringsConfig WHERE Short_Type ='FASHP' AND Config_Name = @FASHP";

            object value = SqlClientUtility.ExecuteScalar(CommandType.Text, sql, sqlParams);

            return value.ToString();

        }
    }
}